package com.tansun.easycare.rule.datasource.service.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageInfo;
import com.tansun.easycare.core.service.impl.CrudServiceImpl;
import com.tansun.easycare.rule.datasource.dao.RuleDatasheetColumnDao;
import com.tansun.easycare.rule.datasource.dao.RuleDatasheetDao;
import com.tansun.easycare.rule.datasource.entity.DatasheetVo;
import com.tansun.easycare.rule.datasource.entity.RuleDatasheet;
import com.tansun.easycare.rule.datasource.entity.RuleDatasheetColumn;
import com.tansun.easycare.rule.datasource.service.RuleDatasheetService;
import com.tansun.easycare.rule.datasource.service.RuleDatasourceService;
import com.tansun.easycare.rule.front.entity.RuleDatasource;

/**
 * 入口参数ServiceImpl
 * @author WYH
 * @version 2018-06-12
 */
@Service("ruleDatasheetService")
@Transactional(readOnly = true)
public class RuleDatasheetServiceImpl extends CrudServiceImpl<RuleDatasheetDao, RuleDatasheet> implements RuleDatasheetService{

	private final Logger log = LoggerFactory.getLogger(this.getClass());
	
	@Autowired
	private RuleDatasourceService datasourceService;
	@Autowired
	private RuleDatasheetColumnDao colDao; 
	
	public RuleDatasheet get(String id) {
		return super.get(id);
	}
	
	public List<RuleDatasheet> findList(RuleDatasheet ruleDatasheet) {
		return super.findList(ruleDatasheet);
	}
	
	public PageInfo<RuleDatasheet> findPage(Page<RuleDatasheet> page, RuleDatasheet ruleDatasheet) {
		return super.findPage(page, ruleDatasheet);
	}
	
	@Transactional(readOnly = false)
	public void save(RuleDatasheet ruleDatasheet) {
		super.save(ruleDatasheet);
	}
	
	@Transactional(readOnly = false)
	public void delete(RuleDatasheet ruleDatasheet) {
		super.delete(ruleDatasheet);
		colDao.deleteBySheet(ruleDatasheet.getId());
	}

	@Override
	public List<RuleDatasheet> findDbTable(RuleDatasource ruleDatasource) throws Exception {
		String sql = null;
		if("oracle".equalsIgnoreCase(ruleDatasource.getDsType())){
			Class.forName("oracle.jdbc.driver.OracleDriver");
			sql = "select table_name,comments from user_tab_comments";
		}else if("mysql".equalsIgnoreCase(ruleDatasource.getDsType())){
			Class.forName("com.mysql.jdbc.Driver");
			sql = "select TABLE_NAME,TABLE_COMMENT from information_schema.TABLES where table_schema = (select database())";
		}else{
			log.warn("不支持的数据库类型:"+ruleDatasource.getDsType());
			return null;
		}
		try(Connection connection = DriverManager.getConnection(ruleDatasource.getDsUrl(), ruleDatasource.getDsUsername(), ruleDatasource.getDsPwd());){
			PreparedStatement stmt = connection.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			List<RuleDatasheet> list = new ArrayList<RuleDatasheet>();
			while(rs.next()){
				RuleDatasheet e = new RuleDatasheet();
				e.setName(rs.getString(1));
				e.setComments(rs.getString(2));
				list.add(e);
			}
			return list;
		}
		
	}

	@Override
	public List<RuleDatasheetColumn> findTableCols(RuleDatasheet entity) throws Exception {
		RuleDatasource datasource = datasourceService.get(entity.getDatasourceId());
		String sql = null;
		if("oracle".equalsIgnoreCase(datasource.getDsType())){
			Class.forName("oracle.jdbc.driver.OracleDriver");
			sql = "SELECT \n" + 
					"			a.table_name,\n" + 
					"			b.column_name column_name,\n" + 
					"      b.data_type data_type,\n" + 
					"      a.comments comments\n" + 
					"FROM user_col_comments a\n" + 
					"    ,user_tab_columns b\n" + 
					"WHERE  a.table_name = ?\n" + 
					"and a.table_name = b.table_name\n" + 
					"and a.column_name = b.column_name";
		}else if("mysql".equalsIgnoreCase(datasource.getDsType())){
			Class.forName("com.mysql.jdbc.Driver");
			sql = "SELECT \n" + 
					"  TABLE_NAME,\n" + 
					"  COLUMN_NAME,\n" + 
					"  COLUMN_TYPE,\n" + 
					"  COLUMN_COMMENT\n" + 
					"FROM\n" + 
					"  INFORMATION_SCHEMA.COLUMNS \n" + 
					"WHERE TABLE_SCHEMA = (select database()) and TABLE_NAME =?";
		}else{
			log.warn("不支持的数据库类型:"+datasource.getDsType());
			return null;
		}
		try(Connection connection = DriverManager.getConnection(datasource.getDsUrl(), datasource.getDsUsername(), datasource.getDsPwd());){
			PreparedStatement stmt = connection.prepareStatement(sql);
			stmt.setString(1, entity.getName());
			ResultSet rs = stmt.executeQuery();
			List<RuleDatasheetColumn> list = new ArrayList<RuleDatasheetColumn>();
			while(rs.next()){
				RuleDatasheetColumn e = new RuleDatasheetColumn();
				e.setName(rs.getString(2));
				e.setColType(rs.getString(3));
				e.setComments(rs.getString(4));
				list.add(e);
			}
			return list;
		}
	}

	@Override
	public void saveOrUpdate(DatasheetVo vo) {
		RuleDatasheet entity = new RuleDatasheet();
		try {
			BeanUtils.copyProperties(entity, vo);
			entity.setIsNewRecord(false);
		}catch (Exception e) {
			e.printStackTrace();
		}
		this.save(entity);
		colDao.deleteBySheet(entity.getId());
		for(RuleDatasheetColumn col:vo.getCols()){
			col.setDatasheetId(entity.getId());
			col.preInsert();
			colDao.insert(col);
		}
		
	}
	
}